﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace PizzaWic.admin
{
    /// <summary>
    /// permet l'acces à la bdd pour l'admin
    /// cette classe ne gère pas les erreurs en cas de mauvais paramètres (type, nombre, valeur)
    /// </summary>
    public class SqlAccess
    {
        private static String connect = "Data Source=localhost\\SQLEXPRESS;Initial Catalog=PizzaWic;Integrated Security=SSPI;Trusted_Connection=True;";

        //liste des nom des paramtres pour chaque procédure stocké
        private static Object[] setParamRawMaterial = { "id_raw_material", "name", "quantity", "unitary_quantity", "alert_quantity", "price" };
        private static Object[] delParamRawMaterial = { "id_raw_material" };
        private static Object[] addParamRawMaterial = { "name", "quantity", "unitary_quantity", "alert_quantity", "price" };

        private static Object[] setParamProduct = { "id_product", "name", "type", "price", "picture" };
        private static Object[] delParamProduct = { "id_product" };
        private static Object[] addParamProduct = { "name", "type", "price", "picture" };

        private static Object[] addParamPRM = { "id_product", "id_raw_material" };

        private static Object[] delParamCooker = { "id_Cook" };
        private static Object[] setParamCooker = { "id_Cook", "name", "surname", "login", "password" };
        private static Object[] addParamCooker = { "name", "surname", "login", "password" };

        private static Object delParamProductRawMaterial = "id_product";

        private static Object[] addParamRawMaterialQuantity = { "id_raw_material", "quantity" };


        /// <summary>
        /// renvoi tous les rawMaterial
        /// </summary>
        /// <returns></returns>
        protected internal DataSet getAllRawMaterial()
        {
            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "raw_material_read", null);
            sqlConnection.Close();
            return dataSet;
        }

        /// <summary>
        /// renvoi la liste des raw material pour un produit donné
        /// </summary>
        /// <param name="parameters">liste des valeurs des paramètres (id_product)</param>
        /// <returns></returns>
        protected internal DataSet getRawMaterialByProduct(Object[] parameters)
        {
            Object[,] param = new Object[2, 1];

            param[1, 0] = parameters[0];
            param[0, 0] = "id_product";

            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "raw_material_by_product", param);
            sqlConnection.Close();
            return dataSet;
        }

        /// <summary>
        /// permet de mettre à jour un rawMaterial
        /// </summary>
        /// <param name="parameters">liste des valeurs des paramètres (id,name,quantity,unitary_quantity,alert_quantity)</param>
        protected internal bool setRawMaterial(Object[] parameters)
        {
            Object[,] param = new Object[2, setParamRawMaterial.Length];
            for (int i = 0; i < parameters.Length; i++)
            {
                param[1, i] = parameters[i];
                param[0, i] = setParamRawMaterial[i];
            }

            SqlConnection sqlConnection = dbConnect();
            psExecute(sqlConnection, "raw_material_update", param);
            sqlConnection.Close();
            return true;
        }

        /// <summary>
        /// supprime un rawMaterial
        /// </summary>
        /// <param name="parameters">liste des paramètres (id_raw_material)</param>
        /// <returns></returns>
        protected internal bool delRawMaterial(Object[] parameters)
        {

            Object[,] param = new Object[2, delParamRawMaterial.Length];
            for (int i = 0; i < parameters.Length; i++)
            {
                param[1, i] = parameters[i];
                param[0, i] = delParamRawMaterial[i];
            }

            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "raw_material_delete", param);
            sqlConnection.Close();
            return true;
        }

        /// <summary>
        /// ajoute un nouveau raw material
        /// </summary>
        /// <param name="parameters">liste des valeurs des paramètres (name,quantity,unitary_quantity,alert_quantity)</param>
        /// <returns></returns>
        protected internal bool addRawMaterial(Object[] parameters)
        {

            Object[,] param = new Object[2, addParamRawMaterial.Length];
            for (int i = 0; i < parameters.Length; i++)
            {
                param[1, i] = parameters[i];
                param[0, i] = addParamRawMaterial[i];
            }
            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "raw_material_insert", param);
            sqlConnection.Close();
            return true;
        }

        /// <summary>
        /// renvoi tous les products
        /// </summary>
        /// <returns></returns>
        protected internal DataSet getAllProducts()
        {
            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "product_read", null);
            sqlConnection.Close();
            return dataSet;
        }

        /// <summary>
        /// permet de mettre à jour un product
        /// </summary>
        /// <param name="parameters">liste des valeurs des paramètres (id_product,name,type,price,picture)</param>
        protected internal bool setProduct(Object[] parameters)
        {
            Object[,] param = new Object[2, setParamProduct.Length];
            for (int i = 0; i < parameters.Length; i++)
            {
                param[1, i] = parameters[i];
                param[0, i] = setParamProduct[i];
            }

            SqlConnection sqlConnection = dbConnect();
            psExecute(sqlConnection, "product_update", param);
            sqlConnection.Close();
            return true;
        }

        /// <summary>
        /// supprime un product
        /// </summary>
        /// <param name="parameters">liste des paramètres (id_product)</param>
        /// <returns></returns>
        protected internal bool delProduct(Object[] parameters)
        {

            Object[,] param = new Object[2, delParamProduct.Length];
            for (int i = 0; i < parameters.Length; i++)
            {
                param[1, i] = parameters[i];
                param[0, i] = delParamProduct[i];
            }

            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "product_delete", param);
            sqlConnection.Close();
            return true;
        }

        /// <summary>
        /// ajoute un nouveau product
        /// </summary>
        /// <param name="parameters">liste des valeurs des paramètres (name,type,price,picture)</param>
        /// <returns></returns>
        protected internal bool addProduct(Object[] parameters)
        {

            Object[,] param = new Object[2, addParamProduct.Length];
            for (int i = 0; i < parameters.Length; i++)
            {
                param[1, i] = parameters[i];
                param[0, i] = addParamProduct[i];
            }
            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "product_insert", param);
            sqlConnection.Close();
            return true;
        }

        /// <summary>
        /// renvoi le dernier id product ajouté en base
        /// </summary>
        /// <returns></returns>
        protected internal int getLastIdProduct()
        {
            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "product_get_last_id", null);
            sqlConnection.Close();
            return int.Parse(dataSet.Tables[0].Rows[0][0].ToString());
        }

        /// <summary>
        /// ajoute les liens entre raw_material et product en base
        /// </summary>
        /// <param name="parameters"></param>
        /// <returns></returns>
        protected internal bool addProductRawMaterial(Object[] parameters)
        {
            Object[,] param = new Object[2, addParamPRM.Length];
            for (int i = 0; i < parameters.Length; i++)
            {
                param[1, i] = parameters[i];
                param[0, i] = addParamPRM[i];
            }
            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "product_raw_material_insert", param);
            sqlConnection.Close();
            return true;
        }

        /// <summary>
        /// permet de se connecter à la base
        /// </summary>
        /// <returns>un objet SqlConnection pour se deconnecter à la fin de la transaction</returns>
        private SqlConnection dbConnect()
        {
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = connect;
            sqlConnection.Open();
            return sqlConnection;
        }


        /// <summary>
        /// permet d'exuter une procédure stockée
        /// </summary>
        /// <param name="sqlConnection"></param>
        /// <param name="ps">le nom de la procédure stockée</param>
        /// <param name="parameters">les paramètres de la procédure stockée</param>
        /// <returns>retourne éventuellement le résultat de la requète</returns>
        private DataSet psExecute(SqlConnection sqlConnection, String ps, Object[,] parameters)
        {
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.CommandText = ps;

            if (parameters != null)
                for (int i = 0; i < parameters.GetLength(1); i++)
                {
                    SqlParameter sqlParameter = new SqlParameter();
                    sqlParameter.ParameterName = (String)parameters[0, i];
                    sqlParameter.Value = parameters[1, i];
                    sqlCommand.Parameters.Add(sqlParameter);
                }

            DataSet dataSet = new DataSet();
            SqlDataAdapter sqlDataAdaptater = new SqlDataAdapter();
            sqlDataAdaptater.SelectCommand = sqlCommand;
            sqlDataAdaptater.Fill(dataSet);

            return dataSet;
        }
        

        //permet de supprimer un cuisinier
        internal bool delCooker(object[] parameters)
        {
            Object[,] param = new Object[2, delParamCooker.Length];
            for (int i = 0; i < parameters.Length; i++)
            {
                param[1, i] = parameters[i];
                param[0, i] = delParamCooker[i];
            }

            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "cook_delete", param);
            sqlConnection.Close();
            return true;
        }

        //renvoi tout les cuisiniers
        internal DataSet getAllCooker()
        {
            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "cook_read", null);
            sqlConnection.Close();
            return dataSet;
        }

        //update d'un cuisnier
        internal bool setCooker(object[] parameters)
        {
            Object[,] param = new Object[2, setParamCooker.Length];
            for (int i = 0; i < parameters.Length; i++)
            {
                param[1, i] = parameters[i];
                param[0, i] = setParamCooker[i];
            }

            SqlConnection sqlConnection = dbConnect();
            psExecute(sqlConnection, "cook_update", param);
            sqlConnection.Close();
            return true;
        }

        //ajout d'un cuisinier
        internal bool addCooker(object[] parameters)
        {
            Object[,] param = new Object[2, addParamCooker.Length];
            for (int i = 0; i < parameters.Length; i++)
            {
                param[1, i] = parameters[i];
                param[0, i] = addParamCooker[i];
            }
            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "cook_insert", param);
            sqlConnection.Close();
            return true;
        }

        //supprime un cuisiner
        internal void delProductRawMaterial(object parameters)
        {
            Object[,] param = new object[2, 1];
            param[1, 0] = parameters;
            param[0, 0] = delParamProductRawMaterial;


            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "product_raw_material_delete", param);
            sqlConnection.Close();

        }

        //ajout des quantité d'une matière première
        internal void addRawMaterialQuantity(object[] parameters)
        {
            Object[,] param = new object[2, 2];

            for (int i = 0; i < parameters.Length; i++)
            {
                param[1, i] = parameters[i];
                param[0, i] = addParamRawMaterialQuantity[i];
            }

            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "raw_material_add_quantity", param);
            sqlConnection.Close();

        }

        internal DataSet getLoginFree(string login)
        {
            Object[,] param = new Object[2, 1];

            param[1, 0] = login;
            param[0, 0] = "login";

            SqlConnection sqlConnection = dbConnect();
            DataSet dataSet = psExecute(sqlConnection, "user_login_free", param);
            sqlConnection.Close();
            return dataSet;
        }
    }
}